package daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import dao.MallDao;
import pojo.Book;
import pojo.BookImage;
import pojo.MallHomeImage;
import pojo.Resource;
import pojo.ShoppingCart;
import utils.DruidUtils;

public class MallDaoImpl implements MallDao{

	
	
	@Override
	public ArrayList<Book> queryBookByName(String book) {
		ArrayList<Book> books = new ArrayList<Book>();
		Connection conn = DruidUtils.getConnection();
		PreparedStatement prep =null;
		ResultSet res=null;
		Book b =null;
		try {
			String sql = "select * from books where bookName like ?";
			prep = conn.prepareStatement(sql);
			prep.setString(1, "%"+book+"%");
		    res = prep.executeQuery();
			while(res.next()) {
				int bookid =res.getInt("bookid");
				int stid =res.getInt("stid");
				String bookName =res.getString("bookName");
				int bookKind =res.getInt("bookKind");
				float bookPrice = res.getFloat("bookPrice");
				float expressPrice = res.getFloat("expressPrice");
				String bookAuthor = res.getString("bookAuthor");
				String bookIntroduction=res.getString("bookIntroduction");
				String bookMainIma =res.getString("bookMainIma");
				b=new Book(bookid,stid,bookName,bookKind,bookPrice,expressPrice,bookAuthor,bookIntroduction,bookMainIma);
				books.add(b);
				

			}

			res.close();
			prep.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return books;
	}

	@Override
	public ArrayList<MallHomeImage> getHomeIma() {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from mallHomeImage";
		ArrayList<MallHomeImage> mhiList = new ArrayList<MallHomeImage>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				MallHomeImage mhi = new MallHomeImage();
				mhi.setMhiid(res.getInt("mhiid"));
				mhi.setBookid(res.getInt("bookid"));
				mhi.setMhIma(res.getString("mhIma"));
				
				mhiList.add(mhi);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mhiList;
	}

	@Override
	public ArrayList<Book> getBooks() {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from books";
		ArrayList<Book> bList = new ArrayList<Book>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				Book b = new Book();
				b.setBookid(res.getInt("bookid"));
				b.setStid(res.getInt("stid"));
				b.setBookName(res.getString("bookName"));
				b.setBookKind(res.getInt("bookKind"));
				b.setBookPrice(res.getFloat("bookPrice"));
				b.setExpressPrice(res.getFloat("expressPrice"));
				b.setBookAuthor(res.getString("bookAuthor"));
				b.setBookIntroduction(res.getString("bookIntroduction"));
				b.setBookMainIma(res.getString("bookMainIma"));
				bList.add(b);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bList;
	}

	@Override
	public ArrayList<BookImage> getBookImages(int bookid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from bookimage where bookid=?";
		ArrayList<BookImage> bImaList = new ArrayList<BookImage>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			pre.setInt(1, bookid);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				BookImage bi = new BookImage();
				bi.setBookid(res.getInt("bookid"));
				bi.setBimaid(res.getInt("bimaid"));
				bi.setBookIma(res.getString("bookIma"));
				bImaList.add(bi);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bImaList;
	}

	@Override
	public ArrayList<ShoppingCart> getCartBook(int userid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "select * from (select books.*,shoppingcart.userid,shoppingcart.scid from books,shoppingcart where books.bookid=shoppingcart.bookid) as temp where userid=?";
		ArrayList<ShoppingCart> cartList = new ArrayList<ShoppingCart>();
		try {
			PreparedStatement pre = conn.prepareStatement(sql);
			pre.setInt(1, userid);
			
			ResultSet res = pre.executeQuery();
			while(res.next()) {
				ShoppingCart sc = new ShoppingCart();
				sc.setScid(res.getInt("scid"));
				sc.setUserid(res.getInt("userid"));
				sc.setBookid(res.getInt("bookid"));
				sc.setStid(res.getInt("stid"));
				sc.setBookName(res.getString("bookName"));
				sc.setBookKind(res.getInt("bookKind"));
				sc.setBookPrice(res.getFloat("bookPrice"));
				sc.setExpressPrice(res.getFloat("expressPrice"));
				sc.setBookAuthor(res.getString("bookAuthor"));
				sc.setBookIntroduction(res.getString("bookIntroduction"));
				sc.setBookMainIma(res.getString("bookMainIma"));
				cartList.add(sc);
			}
			res.close();
			pre.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cartList;
	}

	@Override
	public int getCartBook(int userid, int bookid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "insert into shoppingCart values(0,?,?)";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			//pre.setString(1, rsName);
			pre.setInt(1, userid);
			//pre.setString(3, rsDes);
			pre.setInt(2, bookid);
			//pre.setString(5, DateUtils.formDate(new Date(), "yyyy-MM-dd hh:mm:ss"));
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int carRemove(int scid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "delete from shoppingCart where scid = ?";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, scid);

			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int toPay(int userid, float money) {
		Connection conn = DruidUtils.getConnection();
		String sql = "update user set money=? where userid=?;";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setFloat(1, money);
			pre.setInt(2, userid);
			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int emptyCart(int userid) {
		Connection conn = DruidUtils.getConnection();
		String sql = "delete from shoppingCart where userid = ?";
		PreparedStatement pre = null;
		try {
			pre = conn.prepareStatement(sql);
			pre.setInt(1, userid);

			return pre.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}
